/*
	exec sp_assembly_release_report_proc '0','0','0','0','0','0','DETAIL','XLREPORT','0'
	exec sp_assembly_release_report_proc '0','0','0','0','3/30/2009','5/18/2009','DETAIL','XLREPORT','0'
*/
ALTER proc sp_assembly_release_report_proc

@PROCESSSTEP_IDS	VARCHAR(200),
@VENDORID		VARCHAR(50),
@STATUS			VARCHAR(50),
@CATEGORY		VARCHAR(50),
@FROMDATE		VARCHAR(50),
@TODATE			VARCHAR(50),
@ISDETAIL		VARCHAR(50),
@REPORTTYPE		VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN

DECLARE @SQLSTRING VARCHAR(4000)

DECLARE @RELEASE_CURSOR	CURSOR
DECLARE @WAFER_CURSOR	CURSOR
DECLARE @LOT_CURSOR	CURSOR

DECLARE @WAFER_IDS_TABLE TABLE ( LOT_ID INT, WAFER_ID INT )

DECLARE @RELEASE_ID INT
DECLARE @SHIPPED_LOT_NUMBER VARCHAR(50)
DECLARE @WAFER_ID  VARCHAR(10)
DECLARE @WAFER_IDS VARCHAR(200)
DECLARE @LOT_ID INT
DECLARE @LOT_NUMBER VARCHAR(50)
DECLARE @LOT_NUMBERS VARCHAR(200)
DECLARE @DB_INSTRUCTIONS VARCHAR(500)
DECLARE @LOTWAFER_IDS	VARCHAR(200)

CREATE TABLE #RELEASE_TABLE 
			([ASSM REL ID]int,[ASSM RELEASE NO] VARCHAR(50), [RELEASE DATE] DATETIME, [PO NO] VARCHAR(50),
			 [FROM VENDOR] VARCHAR(50), [TO VENDOR] VARCHAR(50), [PACKAGE TYPE] VARCHAR(50),
			 [REQUEST TYPE] VARCHAR(50), [ASSM REVISION NO] VARCHAR(50),[TEST LOCATION] VARCHAR(50),
			 [STATUS] VARCHAR(50),[SHIP TO VENDOR] VARCHAR(50), [PIN COUNT] VARCHAR(50),
			 [REEL QTY] INT, [TURN KEY] VARCHAR(50), [INSTRUCTIONS] VARCHAR(500), 
			 [FAB LOT NUMBER] VARCHAR(200),[ASSEMBLY LOT NUMBER] VARCHAR(50), [WAFER QTY] INT,[WAFER ID] VARCHAR(1000),[QTY PULLED] INT,
			 [BIN INFO] VARCHAR(50),[PART NUMBER] VARCHAR(50), [ASSM START DATE] DATETIME )

SET @SQLSTRING = N' INSERT INTO #RELEASE_TABLE
		SELECT 
			 ART.ASSEMBLY_RELEASE_ID,ASSEMBLY_RELEASE_NO,min(ASSEMBLY_RELEASE_DATE),PO_NUMBER,
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = FROM_VENDOR_ID ),
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = TO_VENDOR_ID ),
			PACKAGE_TYPE,REQUEST_TYPE,ASSM_REVESION_NUMBER,
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = TEST_LOCATION_ID ),STATUS,
			( SELECT V_NAME FROM VENDOR WHERE VENDOR_ID = SHIP_TO_VENDOR_ID ),PIN_COUNT,
			( SELECT TOP 1 REEL_QTY FROM REEL_QTY_TABLE WHERE PACKAGE= PACKAGE_TYPE AND PIN_COUNT= PIN_COUNT ),
			(CASE 
				WHEN ( TURN_KEY = 0 AND IS_TEST = 0 AND IS_TAPE_REEL = 0 ) THEN ''ASSM+TEST+T&R''
				WHEN ( TURN_KEY = 0 AND IS_TEST = 0 AND IS_TAPE_REEL = 1 ) THEN ''ASSM+TEST''
				ELSE ''ASSEMBLY''
			END),
			null,NULL,shipped_lot_number,
			count(WAFER_ID),null,sum(QTY_PULLED),ARLT.BIN_INFO,P.PART_NUMBER
		   ,(SELECT TOP 1 DATE_IN FROM LOT_TABLE WHERE FAB_LOT_NUMBER = ARLT.shipped_lot_number AND PROCESSSTEP_ID = ART.PROCESSSTEP_ID AND ART.STATUS <> ''CANCELLED'' ) 
		FROM 
			ASSM_RELEASE_TABLE ART, ASSM_RELEASE_LOT_TABLE ARLT,PRODUCT P
		WHERE 
			ART.ASSEMBLY_RELEASE_ID = ARLT.ASSEMBLY_RELEASE_ID
			AND ART.PROCESSSTEP_ID = P.PROCESSSTEP_ID '

IF @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING + ' AND ART.PROCESSSTEP_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( '''+ @PROCESSSTEP_IDS+''') )'
END

IF @VENDORID IS NOT NULL AND @VENDORID <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING + ' AND TO_VENDOR_ID = '''+ convert(varchar,@VENDORID)+ ''''
END

IF @STATUS IS NOT NULL AND @STATUS <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING + ' AND STATUS = '''+ @STATUS+ ''''
END

IF @CATEGORY IS NOT NULL AND @CATEGORY <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING + ' AND REQUEST_TYPE = '''+ @CATEGORY+ ''''
END

IF @FROMDATE IS NOT NULL AND @FROMDATE <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING +' AND CONVERT(DATETIME,ASSEMBLY_RELEASE_DATE) >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
END

IF @TODATE IS NOT NULL AND @TODATE <> '0'
BEGIN
	SET @SQLSTRING = @SQLSTRING +' AND CONVERT(DATETIME,ASSEMBLY_RELEASE_DATE) <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
END

set @SQLSTRING = @SQLSTRING +' GROUP BY 
				ART.ASSEMBLY_RELEASE_ID,ASSEMBLY_RELEASE_NO,shipped_lot_number,PO_NUMBER,FROM_VENDOR_ID,TO_VENDOR_ID,
				PACKAGE_TYPE,REQUEST_TYPE,ASSM_REVESION_NUMBER,TEST_LOCATION_ID,STATUS,SHIP_TO_VENDOR_ID,
				PIN_COUNT,TURN_KEY,IS_TEST,IS_TAPE_REEL,ARLT.BIN_INFO,P.PART_NUMBER,ART.PROCESSSTEP_ID '
--print'@SQLSTRING :-'+@SQLSTRING
EXEC (@SQLSTRING)

--SELECT * FROM #RELEASE_TABLE
	
	SET @RELEASE_CURSOR = CURSOR FOR SELECT [ASSM REL ID], [ASSEMBLY LOT NUMBER]  FROM #RELEASE_TABLE
	OPEN @RELEASE_CURSOR
	FETCH NEXT FROM @RELEASE_CURSOR INTO @RELEASE_ID, @SHIPPED_LOT_NUMBER
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @DB_INSTRUCTIONS = DB_INSTRUCTIONS FROM ASSM_RELEASE_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID
		
		IF @ISDETAIL = 'DETAIL'
		BEGIN
			SET @LOT_CURSOR = CURSOR FOR SELECT DISTINCT LOT_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID AND Shipped_lot_Number = @SHIPPED_LOT_NUMBER

			OPEN @LOT_CURSOR
			FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				DELETE FROM @WAFER_IDS_TABLE
				
				SET @WAFER_IDS = ''	
	
				INSERT INTO @WAFER_IDS_TABLE 
				SELECT LOT_ID,WAFER_ID FROM ASSM_RELEASE_LOT_TABLE WHERE ASSEMBLY_RELEASE_ID = @RELEASE_ID AND  Shipped_lot_Number = @SHIPPED_LOT_NUMBER

				SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID
				WHILE ( @@ROWCOUNT > 0 ) 
				BEGIN
					IF ( @WAFER_IDS = '' )
					BEGIN
						SET @WAFER_IDS = @WAFER_ID
					END
					ELSE
					BEGIN 
						SET @WAFER_IDS = @WAFER_IDS +','+ @WAFER_ID
					END
					
					DELETE FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID AND WAFER_ID = @WAFER_ID  
											
					SELECT TOP 1 @WAFER_ID = WAFER_ID FROM @WAFER_IDS_TABLE WHERE LOT_ID = @LOT_ID
				END

				SELECT @LOT_NUMBER = LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
				
				IF @LOT_NUMBERS IS NULL
				BEGIN
					SET @LOT_NUMBERS = @LOT_NUMBER
					SET @LOTWAFER_IDS = @WAFER_IDS
				END
				ELSE
				BEGIN
					SET @LOT_NUMBERS = @LOT_NUMBERS + '/'+@LOT_NUMBER
					SET @LOTWAFER_IDS = @LOTWAFER_IDS +'/'+@WAFER_IDS
				END
				
			FETCH NEXT FROM @LOT_CURSOR INTO @LOT_ID
			END
			CLOSE @LOT_CURSOR
			DEALLOCATE @LOT_CURSOR

			UPDATE #RELEASE_TABLE 
				SET [WAFER ID] = @LOTWAFER_IDS, [FAB LOT NUMBER] = @LOT_NUMBERS,
				    [INSTRUCTIONS] = @DB_INSTRUCTIONS
			WHERE 
				[ASSM REL ID] = @RELEASE_ID AND [ASSEMBLY LOT NUMBER] = @SHIPPED_LOT_NUMBER
		END
		ELSE
		BEGIN
			
			UPDATE #RELEASE_TABLE 
				SET [INSTRUCTIONS] = @DB_INSTRUCTIONS, [FAB LOT NUMBER] = @LOT_NUMBER
			WHERE 
				[ASSM REL ID] = @RELEASE_ID
		END
		SET @LOT_NUMBER = NULL
		SET @WAFER_IDS = NULL
		SET @LOT_NUMBERS = NULL
		SET @LOTWAFER_IDS = NULL
		SET @DB_INSTRUCTIONS = NULL

		DELETE FROM @WAFER_IDS_TABLE

	FETCH NEXT FROM @RELEASE_CURSOR INTO @RELEASE_ID, @SHIPPED_LOT_NUMBER
	END
	CLOSE @RELEASE_CURSOR
	DEALLOCATE @RELEASE_CURSOR
	

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN	
		IF @ISDETAIL = 'DETAIL'
		BEGIN
			SELECT 
				 [RELEASE DATE],[PART NUMBER],[ASSM RELEASE NO], [ASSM REVISION NO],[STATUS], 
				 [FAB LOT NUMBER],[ASSEMBLY LOT NUMBER],[ASSM START DATE],[WAFER ID], [WAFER QTY],[QTY PULLED], [FROM VENDOR],[TO VENDOR],
				 [TEST LOCATION], [SHIP TO VENDOR], [PACKAGE TYPE], [PO NO],[REQUEST TYPE],
				 [PIN COUNT], [REEL QTY],[TURN KEY],[INSTRUCTIONS]
			FROM 
				#RELEASE_TABLE
			ORDER BY [RELEASE DATE],[ASSM RELEASE NO] DESC
		END
		ELSE
		BEGIN
			SELECT 
				 [RELEASE DATE], [PART NUMBER], [ASSM RELEASE NO], [ASSM REVISION NO], [STATUS],
				 [ASSEMBLY LOT NUMBER],[ASSM START DATE],[QTY PULLED], [FROM VENDOR],[TO VENDOR],[TEST LOCATION],[SHIP TO VENDOR],
				 [PACKAGE TYPE],[PO NO],[REQUEST TYPE],
				 [PIN COUNT],[REEL QTY],[TURN KEY],[INSTRUCTIONS]
			FROM 
				#RELEASE_TABLE
			ORDER BY [RELEASE DATE],[ASSM RELEASE NO] DESC
		END
	END
	ELSE
	BEGIN
		IF @ISDETAIL = 'DETAIL'
		BEGIN
			SELECT 
				 [ASSM REL ID],[ASSM RELEASE NO], [RELEASE DATE], [PO NO],[FROM VENDOR],[TO VENDOR],
				 [PACKAGE TYPE],[REQUEST TYPE],[ASSM REVISION NO],[TEST LOCATION],[STATUS],
				 [SHIP TO VENDOR],[PIN COUNT],[REEL QTY],[TURN KEY],[INSTRUCTIONS],[FAB LOT NUMBER],
				 [WAFER QTY],[WAFER ID],[QTY PULLED],[BIN INFO],[PART NUMBER]
			FROM 
				#RELEASE_TABLE
			ORDER BY [RELEASE DATE] DESC
		END
		ELSE
		BEGIN
			SELECT 
				 [ASSM REL ID],[ASSM RELEASE NO], [RELEASE DATE], [PO NO],[FROM VENDOR],[TO VENDOR],
				 [PACKAGE TYPE],[REQUEST TYPE],[ASSM REVISION NO],[TEST LOCATION],[STATUS],
				 [SHIP TO VENDOR],[PIN COUNT],[REEL QTY],[TURN KEY],[INSTRUCTIONS],
				 [QTY PULLED],[BIN INFO],[PART NUMBER]
			FROM 
				#RELEASE_TABLE
			ORDER BY [RELEASE DATE] DESC
		END
	END

END
